//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectType;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class GraduateProjectTypeDao {
    private GraduateProjectTypeDao(){
    }
    private static GraduateProjectTypeDao graduateProjectTypeDao = new GraduateProjectTypeDao();

    public static GraduateProjectTypeDao getInstance() {
        return graduateProjectTypeDao;
    }

    public int count(Connection connection) throws SQLException {
        return CommonDao.getInstance().count(connection,"GraduateProjectType");
    }

    public Collection<GraduateProjectType> findAll(List conditionList_json,
                                                   Pagination pagination,
                                                   Connection connection)
            throws SQLException {
        Collection<GraduateProjectType> graduateProjectTypes = new HashSet<GraduateProjectType>();
        int totalNum = GraduateProjectDao.getInstance().count(connection);

        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProjectType");
        if (conditionList_json != null) {
            String whereClause = Condition.toWhereClause(conditionList_json);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum));
        }
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());

        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            //根据id查找结果创建school对象
            GraduateProjectType graduateProjectType = new GraduateProjectType(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"));
            graduateProjectTypes.add(graduateProjectType);
        }
        //关闭对象
        JdbcHelper.close(pstmt,connection);
        return graduateProjectTypes;
    }

    public GraduateProjectType find(Integer id,Connection connection) throws SQLException{
        //创建sql语句
        String findGra_sql = "select * from GraduateProjectType where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(findGra_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        GraduateProjectType graduateProjectType = new GraduateProjectType(id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭连接
        JdbcHelper.close(pstmt,null);
        //返回school对象
        return graduateProjectType;
    }

    public boolean update(GraduateProjectType graduateProjectType,
                          Connection connection)
            throws SQLException{
        //创建sql语句
        String updateGra_sql = "UPDATE GraduateProjectType set  " +
                "description=? ,no=? , remarks=? " +
                "where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGra_sql);

        pstmt.setString(1,graduateProjectType.getDescription());
        pstmt.setString(2,graduateProjectType.getNo());
        pstmt.setString(3,graduateProjectType.getRemarks());
        pstmt.setInt(4,graduateProjectType.getId());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);

        return totalNum > 0;
    }

    public boolean add(GraduateProjectType graduateProjectType,Connection connection)
            throws SQLException{
        //创建sql语句
        String addGra_sql = "INSERT INTO GraduateProjectType" +
                "(description,no,remarks) VALUES" +
                "(?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGra_sql);

        pstmt.setString(1,graduateProjectType.getDescription());
        pstmt.setString(2,graduateProjectType.getNo());
        pstmt.setString(3,graduateProjectType.getRemarks());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);
        return totalNum > 0;
    }

    public boolean delete(GraduateProjectType graduateProjectType,Connection connection)
            throws SQLException{
        //创建sql语句
        String deleteGra_sql = "DELETE FROM GraduateProjectType WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGra_sql);
        //为预编译参数赋值
        pstmt.setInt(1,graduateProjectType.getId());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,connection);

        return totalNum > 0;
    }
}
